set more off
capture log close
capture clear all
*log using National_banks_crisis5.log, text replace
set memory 200m

/***!***!***!***!***!*** [National_banks_crisis_2018_b.do ] ***!***!***!***!***!
*
* Project: National Banks  		
* Programmer:  Scott Fulford, revised and cleaned up by Felipe Schwartzman
               Edited from National_banks_crisis_5.do File in Google Docs, downloaded on 01/22/18
*
* Date:    	 Jan 2018
*
* Auditor:      
* Audit Date:   
*
* Purpose:      
* 1) Create Aggregate national bank leverage figure
* 2) 
* 3)
* 		      
*
* Ouputs: 
*		
* Sources: National_bank_call_reports
					Treasury_gold_monthly.csv from Capital_flows.xlsx which uses
							treaury annual report to get holdings of gold and silver.
							Gold imports and gold production.
					Treasury_gold_monthly.csv
					Gold_silver_production.csv
					gold_imports.csv
					from Capital_flows.xlsx

*
***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***/


/***Define Global Directory ****/

//Change rootdir to where store files
	global ROOTDIR "F:/Dropbox/national banks peg/Replication_files"
	local PROGDIR  "$ROOTDIR"
	local INDIR  "$ROOTDIR/data"
	local OUTDIR  "$ROOTDIR/data"
/*******************************/

cd "`INDIR'"

/**********
ALL COMMERCIAL BANKS Leverage GRAPH
ONLY YEARLY
From National Monetary Commission 1910

************/
import excel "Capital_flows.xlsx", sheet("Com_bank_leverage") firstrow clear

/*Leverage we get is Equity/(Assets-Equity)
Leverage we use in rest of paper is Debt/Assets = (A-E)/A
L2= E/(A-E)
1+L2 = A/(A-E)
1/(1+L2) = our defintion

Our definition of equity is a slightly different than appears
to be from NMC which is Capital +Surplus, we include undivided profits and undistrib dividends 
*/
gen l_state_banks = 1/(1+state_banks/100)
gen l_national_banks = 1/(1+national_banks/100)
gen l_all_banks  = 1/(1+all_banks/100)

label var l_state_banks "State Banks"
label var l_national_banks "National Banks"
label var l_all_banks "All Commercial Banks"


local textplace .77
twoway  (line  l_national_banks year, lpattern(dash) yaxis(1)) ///   
		(line  l_state_banks year,  yaxis(1)) ///
		(line  l_all_banks year, lwidth(thick) yaxis(1)) if year >=1880, ///
  /// tlabel(01jan1880 01jan1884 01jan1888 01jan1892 01jan1896 01jan1900 01jan1904 01jan1908 01jan1912) ///
  xline(1890 1893 1896 1900 1907, lcolor(gs10)) /// 
  text(.83  1890 "Sherman Silver" "Purchase Act", just(right) placement(w) size(small)) /// 
  text(.83  1893 "1893" "Panic", just(right) placement(w) size(small)) ///
  text(.83  1896 "1896" "Election", just(right) placement(w) size(small))  /// 
  text(.73 1900 "Gold" "Standard" "Act" , just(right) placement(w) size(small)) ///
  text(.73 1907 "1907" "Panic", just(right) placement(w) size(small)) ///
  legend(on) /// 
  xlabel(1880(5)1910) ///
  ytitle("Leverage (Debt/Assets)", axis(1)) ///
  name(leverage4, replace) ///
  xtitle(Year) 
 
  cd "`OUTDIR'"
  graph export all_bank_leverage.pdf,  as(pdf) name(leverage4) font(times) replace 




/**********
OVERALL NATIONAL BANKING TIME SERIES
CREATES LEVERAGE GRAPH
************/
cd "`INDIR'"
use National_bank_call_reports_aggregate, clear
replace total_assets = total_assets - bonds_circulation_assets

tsset  call_date
format call_date %tdCCYY


/*Define and label variables*/


gen r_specie_assets = specie_assets/total_assets
label var r_specie_assets "Fraction assets specie"

gen r_assets_equity = total_assets/(capital_stock_liab+ surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab)
label var r_assets_equity "Ratio assets to equity"

gen ln_r_assets_equity = log((total_assets-(capital_stock_liab+ surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab)) /total_assets)
label var ln_r_assets_equity "log Leverage"
gen r_debt_assets = (total_assets-(capital_stock_liab+ surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab)) /total_assets
label var r_debt_assets "Leverage (debt/assets)"


//gen leverage = (total_assets-(capital_stock_liab+ surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab)) /(capital_stock_liab+ surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab)
//label var leverage "Leverage"

//FIGURE 3
local textplace .77
twoway  (tsline r_debt_assets , yaxis(1) lwidth(thick))  (tsline r_specie_assets , yaxis(2) ) , ///
  tlabel(01jan1880 01jan1884 01jan1888 01jan1892 01jan1896 01jan1900 01jan1904 01jan1908 01jan1912) ///
  tline(1jul1890 1oct1893 1dec1896 1mar1900 1dec1907, lcolor(gs10)) /// 
  ttext(.8  1may1890 "Sherman Silver" "Purchase Act", just(right) placement(w) size(small)) /// 
  ttext(.8  1oct1893 "1893" "Panic", just(right) placement(w) size(small)) ///
  ttext(.8  1nov1896 "1896" "Election", just(right) placement(w) size(small))  /// 
  ttext(.64 1mar1900 "Gold" "Standard" "Act" , just(right) placement(w) size(small)) ///
  ttext(.64 1dec1907 "1907" "Panic", just(right) placement(w) size(small)) ///
  legend(on)  ///
  ytitle("Leverage", axis(1)) ///
  name(leverage3, replace) ///
  xtitle(Call Date) ///
  
  cd "`OUTDIR'"
  graph export nb_leverage3.pdf,  as(pdf) name(leverage3) font(times) replace 




/*Create area graph of assets
order creates assending values last is assets
*/
#delim ;
gen banks_assets = national_banks_assets + state_banks_assets;
gen bonds_assets= bonds_circulation_assets+ bonds_deposits_assets;
gen stocks_bonds_assets= us_bonds_assets+ other_stocks_assets;

local colorint = .60;
local assetnum =1;
gen asset0 = specie_assets/1000000;
label var asset0 "specie_assets";
local graphcommand (tsline asset0, recast(area) fcolor(*`colorint'));
foreach var in  banks_assets bonds_assets stocks_bonds_assets reserves_assets clearing_house_assets loans_assets  {;
  local assetnum_pre = `assetnum'-1;
  gen asset`assetnum' = asset`assetnum_pre' +`var'/1000000;
  label var asset`assetnum' "`var'";
  local graphcommand (tsline asset`assetnum', recast(area) fcolor(*`colorint')) `graphcommand';
  local assetnum = `assetnum'+1;
};
disp "`graphcommand'";
gen asset`assetnum' = total_assets/1000000; /*The overall total*/
local graphcommand (tsline asset`assetnum', recast(area) fcolor(*`colorint')) `graphcommand';

twoway `graphcommand' if call_date <=date("01jan1901","DMY") & call_date >=date("28dec1886","DMY"),
  tlabel(1jan1887 1jan1889 1jan1891 1jan1893 1jan1895 1jan1897 1jan1899 ) 
  tmtick(1jan1888 1jan1890 1jan1892 1jan1894 1jan1896 1jan1898 1jan1900)  
  tline(03oct1893 06oct1896, lcolor(navy)) legend(off)  
  ytitle("National bank assets ($ millions)" )  
  title(National bank assets) name(nb_assets, replace) xtitle(Call Date) 
  ttext(4500 06oct1896 "Oct. 1896", placement(e)) ttext(4500 03oct1893 "Oct. 1893", placement(w))
  ttext(150  1jun1900 "Specie", placement(w))
  ttext(500  1jun1900 "Due from other banks", placement(w))
  ttext(800 1jun1900 "Bonds for circulation and deposits", placement(w))
  ttext(1100 1jun1900 "Stocks and bonds", placement(w))
  ttext(1500 1jun1900 "Reserves", placement(w))
  ttext(1800 1jun1900 "Clearing house exchanges", placement(w))
  ttext(2800 1jun1900 "Loans and discounts", placement(w))
  ttext(5000 1jun1900 "Other assets", placement(w))
legend(off)
  ;
  
twoway `graphcommand' ,
  tlabel(1jan1880 1jan1885 1jan1890 1jan1895 1jan1900 1jan1905 1jan1910  ) 
  /*tmtick(1jan1880 1jan1881 1jan1882 1jan1883 1jan1884 1jan1885 1jan1886 1jan1887 1jan1888 1jan1892 1jan1894 1jan1896 1jan1898 1jan1900)*/  
  tline(1jul1890 1oct1893 1nov1896 1dec1907, lcolor(gs10)) /// 
  ttext(9500 1may1890 "Sherman Silver" "Purchase Act", just(right) placement(w) size(medsmall)) /// 
  ttext(9500 1oct1893 "1893" "Panic", just(right) placement(w) size(medsmall)) ///
  ttext(9500 1nov1896 "1896" "Election", just(right) placement(w) size(medsmall))  /// 
  ttext(9500 1dec1907 "1907" "Panic", just(right) placement(w) size(medsmall)) ///
  ytitle("National bank assets ($ millions)" ) legend(off)   
  title(National bank assets) name(nb_assets2, replace) xtitle(Call Date) 
  ttext(300  1jun1910 "Specie", placement(w))
  ttext(900  1jun1910 "Due from other banks", placement(w))
  ttext(1500 1jun1910 "Bonds for circulation and deposits", placement(w))
  ttext(2200 1jun1910 "Stocks and bonds", placement(w))
  ttext(3000 1jun1910 "Reserves", placement(w))
  ttext(3700 1jun1910 "Clearing house exchanges", placement(w))
  ttext(5500 1jun1910 "Loans and discounts", placement(w))
  ttext(8000 1jun1910 "Other assets", placement(w))
legend(off)
  ;  

graphexportpdf nb_assets, name(nb_assets);
graph export nb_assets.eps,  name(nb_assets) font(times) replace ;

 /*Create area graph of liabilities
*/
local colorint = .60;
gen banks_liab = due_national_banks_liab+ due_state_banks_liab;
gen equity_liab =  surplus_fund_liab+ undivided_profits_liab+ dividends_unpaid_liab;
local liabnum =1;
gen liab0 = capital_stock_liab/1000000;
label var liab0 "national_circulation_liab";
local graphcommand (tsline liab0, recast(area) fcolor(*`colorint'));
foreach var in equity_liab  national_circulation_liab due_state_banks_liab due_national_banks_liab   individual_deposits_liab  {;
  local liabnum_pre = `liabnum'-1;
  gen liab`liabnum' = liab`liabnum_pre' +`var'/1000000;
  label var liab`liabnum' "`var'";
  local graphcommand (tsline liab`liabnum', recast(area) fcolor(*`colorint')) `graphcommand';
  local liabnum = `liabnum'+1;
};
disp "`graphcommand'";
gen liab`liabnum' = total_liab/1000000; /*The overall total*/
local graphcommand (tsline liab`liabnum', recast(area) fcolor(*`colorint')) `graphcommand';
twoway `graphcommand' if call_date <=date("01jan1901","DMY") & call_date >=date("28dec1886","DMY") ,
  tlabel(1jan1887 1jan1889 1jan1891 1jan1893 1jan1895 1jan1897 1jan1899 ) 
  tmtick(1jan1888 1jan1890 1jan1892 1jan1894 1jan1896 1jan1898 1jan1900)  
  tline(03oct1893 06oct1896, lcolor(navy)) legend(off)  
  ytitle("National bank liabilities ($ millions)" )  
  title(National bank liabilities) name(nb_liab, replace) xtitle(Call Date) 
  ttext(4500 06oct1896 "Oct. 1896", placement(e)) ttext(4500 03oct1893 "Oct. 1893", placement(w))
  ttext(50 1jun1900 "Capital stock", placement(w))
  ttext(350  1jun1900 "Other equity", placement(w))
  ttext(750  1jun1900 "National bank notes", placement(w))
  ttext(1200  1jun1900 "Due state banks", placement(w))
  ttext(1600  1jun1900 "Due national banks", placement(w)) 
  ttext(3000 1jun1900 "Individual deposits", placement(w))
  ttext(5000 1jun1900 "Other liabilities", placement(w))
 ;
graphexportpdf nb_liab, name(nb_liab);
graph export nb_liab.eps,  name(nb_liab) font(times) replace ;

 
twoway `graphcommand' ,
  tlabel(1jan1880 1jan1885 1jan1890 1jan1895 1jan1900 1jan1905 1jan1910  ) 
  /*tmtick(1jan1880 1jan1881 1jan1882 1jan1883 1jan1884 1jan1885 1jan1886 1jan1887 1jan1888 1jan1892 1jan1894 1jan1896 1jan1898 1jan1900)*/  
  tline(1jul1890 1oct1893 1nov1896 1dec1907, lcolor(gs10)) /// 
  ttext(9500 1may1890 "Sherman Silver" "Purchase Act", just(right) placement(w) size(medsmall)) /// 
  ttext(9500 1oct1893 "1893" "Panic", just(right) placement(w) size(medsmall)) ///
  ttext(9500 1nov1896 "1896" "Election", just(right) placement(w) size(medsmall))  /// 
  ttext(9500 1dec1907 "1907" "Panic", just(right) placement(w) size(medsmall)) ///
  ytitle("National bank liabilities ($ millions)" )  legend(off)
  title(National bank liabilities) name(nb_liab2, replace) xtitle(Call Date) 
  ttext(400 1jun1910 "Capital stock", placement(w))
  ttext(1400  1jun1910 "Other equity", placement(w))
  ttext(2000 1jun1910 "National bank notes", placement(w))
  ttext(2700 1jun1910 "Due state banks", placement(w))
  ttext(3300  1jun1910 "Due national banks", placement(w)) 
  ttext(5000 1jun1910 "Individual deposits", placement(w))
  ttext(8000 1jun1910 "Other liabilities", placement(w))
  
 ; 
 graph combine nb_assets2 nb_liab2, cols(1) xsize(4) ysize(6) name(nb_assets_liab, replace);
graph export nb_assets_liab.pdf, name(nb_assets_liab) replace font(times);
 
#delim cr 
 exit
   exit	 


/************
Do simple cross section analysis
SLF version for initial analysis
*/

/*To use: By all cities/states*/
use National_bank_call_reports, replace

/*To use Cities combined into states
use National_bank_call_reports_states, replace
*/


/*Create variables which are 1 in the year after 1893 crisis and 1896 election*/
gen P1893 = (call_date >= date("03oct1893", "DMY") & call_date <= date("02oct1894", "DMY"))
gen P1896 = (call_date >= date("17dec1896", "DMY") & call_date <= date("05oct1897", "DMY"))
gen D1893 = (call_date == date("03oct1893", "DMY"))
gen D1896 = (call_date == date("17dec1896", "DMY")) 

/*Create "season" which is the number of the call report in year
This way of creating it may have some errors at the beginning
*/
sort location call_year call_date
by location call_year: gen call_num = _n

gen r_specie_assets = specie_assets/total_assets
label var r_specie_assets "Fraction assets specie"







sort location
